package com.dha.dao.impl;

import java.util.List;

import javax.jws.WebMethod;
import javax.jws.WebParam;
import javax.jws.WebService;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

import com.dha.custom.XemDiemCustom;
import com.dha.custom.XemDiemTongKetKyCustom;
import com.dha.dao.XemDiemDAO;
import com.dha.entities.HocPhanEntity;
import com.dha.entities.TongKetHocKyEntity;
import com.dha.entities.TongKetKhoaHocEntity;
import com.dha.utility.Utility;

@WebService(targetNamespace = "XemDiemService")
public class XemDiemDAOImpl implements XemDiemDAO {

	private SessionFactory sessionFactory = new Configuration().configure()
			.buildSessionFactory();
	private Session session;

	@Override
	@WebMethod(operationName = "XemDiemSinhVienTatCaHocKy")
	public List<XemDiemCustom> xemDiemTatCaHocKy(@WebParam(name="MaSinhVien") String maSinhVien) {
		try {
			
			
			session = sessionFactory.openSession();
			session.beginTransaction();

			String sql = "select new com.dha.custom.XemDiemCustom(hp.maHocPhan, lhpe.maLopHocPhan, hp.tenHocPhan, hp.soTinChi, "
					+ "lhpsve.diemTongKet, lhpsve.diemChu, lhpe.hocKy, lhpsve.tichLuy, lhpsve.ghiChu) "
					+ "from LopHocPhanEntity lhpe, HocPhanEntity hp, LopHocPhanSinhVienEntity lhpsve "
					+ "where lhpe.maHocPhan = hp.maHocPhan "
					+ "and lhpe.maLopHocPhan = lhpsve.maLopHocPhan "
					+ "and lhpsve.maSinhVien = ? "
					+ "order by lhpsve.diemTongKet desc";
			
//			String sql = "select hpe.maHocPhan, lhpsve.maLopHocPhan, hpe.tenHocPhan, hpe.soTinChi, "
//					+ "lhpsve.diemTongKet, lhpsve.diemChu,lhpe.hocKy, "
//					+ "lhpsve.tichLuy, lhpsve.ghiChu "
//					+ "from LopHocPhanSinhVienEntitye lhpsve, HocPhanEntitye hpe, LopHocPhanEntitye lhpe "
//					+ "where lhpe.maHocPhan = hpe.maHocPhan and lhpe.maLopHocPhan = lhpsve.maLopHocPhan "
//					+ "and lhpsve.maSinhVien = ? order by lhpe.ngayHieuLuc asc";
			

			Query query = session.createQuery(sql);
			query.setParameter(0, maSinhVien);

			List<XemDiemCustom> ketQua = query.list();
			session.getTransaction().commit();
			return ketQua;
		} catch (Exception e) {
			// TODO: handle exception
			session.getTransaction().rollback();
			return null;
		} finally {
			session.close();
		}
	}
	

	@Override
	@WebMethod(operationName = "XemDiemSinhVienTheoHocKy")
	public List<XemDiemCustom> xemDiemTheoHocKy(@WebParam(name="MaSinhVien") String maSinhVien, @WebParam(name="HocKy") int hocKy) {
		try {
			session = sessionFactory.openSession();
			session.beginTransaction();
			String sql = "select DISTINCT new com.dha.custom.XemDiemCustom(hp.maHocPhan, lhpe.maLopHocPhan, hp.tenHocPhan, hp.soTinChi, lhpsve.diemTongKet, lhpsve.diemChu, lhpe.hocKy, lhpsve.tichLuy, lhpsve.ghiChu) "
					+ "from LopHocPhanEntity lhpe, HocPhanEntity hp, LopHocPhanSinhVienEntity lhpsve "
					+ "where lhpe.maHocPhan = hp.maHocPhan "
					+ "and lhpe.maLopHocPhan = lhpsve.maLopHocPhan "
					+ "and lhpsve.maSinhVien = ? "
					+ "and lhpe.hocKy = ?"
					+ "order by lhpsve.diemTongKet desc";

			Query query = session.createQuery(sql);
			query.setParameter(0, maSinhVien);
			query.setParameter(1, hocKy);
			List<XemDiemCustom> ketQua = query.list();

			return ketQua;
		} catch (Exception e) {
			// TODO: handle exception
			session.getTransaction().rollback();
			return null;
		} finally {
			session.close();
		}
	}

	
	@Override
	@WebMethod(operationName="XemDiemTongKetHocKy")
	public List<TongKetHocKyEntity> xemDiemHocKy( @WebParam(name="MaSinhVien") String maSinhVien) {
		try {
			session = sessionFactory.openSession();
			session.beginTransaction();
			String sql = "from TongKetHocKyEntity where maSinhVien = ?";

//			String sql = "select new com.dha.custom.XemDiemTongKetKyCustom(hke.hocKy, hke.soTinChiDangKy, hke.diemTBCHT, hke.xepLoaiHocKy) "
//					+ "from TongKetHocKyEntity hke, LopHocPhanEntity lhpe , LopHocPhanSinhVienEntity lhpsve "
//					+ "where hke.maSinhVien= ? and hke.hocKy = lhpe.hocKy "
//					+ "and lhpe.maLopHocPhan = lhpsve.maLopHocPhan "
//					+ "and lhpsve.maSinhVien = hke.maSinhVien "
//					+ "order by lhpe.ngayHieuLuc desc";
			
			Query query = session.createQuery(sql);
			query.setParameter(0, maSinhVien);
			List<TongKetHocKyEntity> ketQua = query.list();
			session.getTransaction().commit();
			return ketQua;
		} catch (Exception e) {
			// TODO: handle exception
			session.getTransaction().rollback();
			return null;
		} finally {
			session.close();
		}
	}


	@Override
	@WebMethod(operationName="XemDiemKhoaHoc")
	public List<TongKetKhoaHocEntity> xemDiemKhoaHoc(@WebParam(name="MaSinhVien")String maSinhVien) {
		try {
			session = sessionFactory.openSession();
			session.beginTransaction();
			String sql = "from TongKetKhoaHocEntity where maSinhVien = ?";

			Query query = session.createQuery(sql);
			query.setParameter(0, maSinhVien);
			List<TongKetKhoaHocEntity> ketQua = query.list();
			session.getTransaction().commit();
			return ketQua;
		} catch (Exception e) {
			// TODO: handle exception
			session.getTransaction().rollback();
			return null;
		} finally {
			session.close();
		}
	}

	@WebMethod(operationName="XemDiemHocKyHienTai")
	@Override
	public List<XemDiemCustom> xemDiem(@WebParam(name="MaSinhVien") String maSinhVien) {
try {
			
			int hocKy = Utility.layMaHocKyHienTai(maSinhVien);
			session = sessionFactory.openSession();
			session.beginTransaction();

			String sql = "select DISTINCT new com.dha.custom.XemDiemCustom(hp.maHocPhan, lhpe.maLopHocPhan, hp.tenHocPhan, hp.soTinChi, lhpsve.diemTongKet, lhpsve.diemChu, lhpe.hocKy, lhpsve.tichLuy, lhpsve.ghiChu) "
					+ "from LopHocPhanEntity lhpe, HocPhanEntity hp, LopHocPhanSinhVienEntity lhpsve "
					+ "where lhpe.maHocPhan = hp.maHocPhan "
					+ "and lhpe.maLopHocPhan = lhpsve.maLopHocPhan "
					+ "and lhpsve.maSinhVien = ? "
					+ "and lhpe.hocKy = ? "
					+ "order by lhpsve.diemTongKet desc";

			Query query = session.createQuery(sql);
			query.setParameter(0, maSinhVien);
			query.setParameter(1, hocKy);
			List<XemDiemCustom> ketQua = query.list();
			session.getTransaction().commit();
			return ketQua;
		} catch (Exception e) {
			// TODO: handle exception
			session.getTransaction().rollback();
			return null;
		} finally {
			session.close();
		}
	}
	
}
